import pandas as pd

# 读取四张表
profit_df = pd.read_csv("../profit_statement/stock_lrb_em_20250331.csv", dtype={"股票代码": str})
cash_df = pd.read_csv("../cash_flow_statement/cash_flow_statement_20250331.csv", dtype={"股票代码": str})
info_df = pd.read_csv("../basic_info/stock_info.csv", dtype={"股票代码": str})
balance_df = pd.read_csv("../balance_sheet/balance_sheet_20250331.csv", dtype={"股票代码": str})

# 重命名列，便于处理
profit_df.rename(columns={
    "营业总支出-营业支出": "营业支出",
    "营业总支出-销售费用": "销售费用",
    "营业总支出-管理费用": "管理费用",
    "营业总支出-财务费用": "财务费用",
    "营业总支出-营业总支出": "营业总支出"
}, inplace=True)

cash_df.rename(columns={
    "净现金流-净现金流": "净现金流",
    "经营性现金流-现金流量净额": "经营现金流",
    "投资性现金流-现金流量净额": "投资现金流",
    "融资性现金流-现金流量净额": "融资现金流"
}, inplace=True)

balance_df.rename(columns={
    "资产-总资产": "总资产",
    "负债-总负债": "总负债"
}, inplace=True)

# 合并数据（以股票代码为键）
df = profit_df.merge(cash_df, on=["股票代码", "股票简称"], how="inner") \
              .merge(info_df, on=["股票代码", "股票简称"], how="inner") \
              .merge(balance_df, on=["股票代码", "股票简称"], how="inner")

# 去除列名空格
df.columns = df.columns.str.strip()

# 转换为可计算的数字（剔除逗号、空格），排除非数值列
def to_float(x):
    if isinstance(x, str):
        x = x.replace(',', '').replace(' ', '')
    try:
        return float(x)
    except:
        return None

# 排除股票代码和简称列
exclude_cols = ["股票代码", "股票简称"]
for col in df.columns:
    if col not in exclude_cols:
        df[col] = df[col].apply(to_float)


# 新建 DataFrame 存储结果
result_df = pd.DataFrame()
result_df["股票代码"] = df["股票代码"]
result_df["股票简称"] = df["股票简称"]
result_df["净利润率"] = df["净利润"] / df["营业总收入"]
result_df["营业利润率"] = df["营业利润"] / df["营业总收入"]
result_df["ROE"] = df["净利润"] / df["股东权益合计"]
result_df["ROA"] = df["净利润"] / df["总资产"]
result_df["资产负债率"] = df["总负债"] / df["总资产"]
result_df["PE"] = df["总市值"] / df["净利润"]
result_df["PB"] = df["总市值"] / df["股东权益合计"]
result_df["EPS"] = df["净利润"] / df["总股本"]
result_df["每股净资产"] = df["股东权益合计"] / df["总股本"]
result_df["净现金流比率"] = df["经营现金流"] / df["净利润"]
result_df["现金流覆盖率"] = df["经营现金流"] / df["总负债"]
result_df["自由现金流"] = df["经营现金流"] - df["投资现金流"]
result_df["净利润"] = df["净利润"]
result_df["净利润同比"] = df["净利润同比"]

# 重新排列列顺序（可选，放前面好看些）
cols_order = ["股票代码", "股票简称", "净利润", "净利润同比"] + [col for col in result_df.columns if col not in ["股票代码", "股票简称", "净利润", "净利润同比"]]
result_df = result_df[cols_order]   

numeric_cols = result_df.columns.difference(["股票代码", "股票简称"])
result_df[numeric_cols] = result_df[numeric_cols].round(5)

# 导出结果
result_df.to_csv("financial_indicators_20250331.csv", index=False)


print("✅ 指标已单独保存为 financial_indicators_20250331.csv")
